﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetStaffInPosition]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetStaffInPosition];
GO
CREATE PROCEDURE [dbo].[sproc_GetStaffInPosition]
    @Position_id int,
    @Dimission bit = 0
/*

============================================================
功能:    得到职位人员
参数:
    @Position_id int        :    职位ID
    @Dimission bit        :    是否在职

============================================================

*/
AS
BEGIN
SET NOCOUNT ON

SELECT
	a.*,
	(
		case
			when a.birthday is NULL then N'-'
			when datediff(dd, a.birthday, N'1900-01-01')=0 then N'-'
			else convert(nvarchar,datediff(yy,a.birthday,getdate()))
		end
	) as Age,
	(case sex when 1 then N'男' else N'女' end) as SexName,
	convert(nvarchar(10), a.RegistedDate, 120) AS RQ,
	(SELECT Position_name FROM uds_Position WHERE Position_id = b.Position_id) AS Position_Name
FROM
	uds_staff a,
	uds_staff_in_position b
WHERE
	a.dimission = @Dimission 
	and a.staff_id = b.staff_id
	and b.Position_id = @Position_id
ORDER BY
	a.RegistedDate desc

END
GO